﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;


namespace HCDV.HCSP
{
    /// <summary>
    /// 一般性通用的增/删/改/查询 调用
    /// </summary>
    public class dbsimpleold : IDisposable
    {
        private static bool ischeck = false;
        public string conStr;
        public SqlConnection con;
        public SqlDataAdapter commandAdp;

        /// <summary>
        /// 日志类
        /// </summary>

        public dbsimpleold()
        {
            conStr = BaseConfig.ConnectionString;
            con = new SqlConnection(conStr);
            commandAdp = new SqlDataAdapter();
        }
        public dbsimpleold(Type t)
        {

            conStr = BaseConfig.ConnectionString;
            con = new SqlConnection(conStr);
            commandAdp = new SqlDataAdapter();
        }
        public string paramChg(string str)
        {
            str = "@" + str;
            return str;
        }
        public long GetSequence(string fieldName)
        {

            SqlParameter parmOut = new SqlParameter();
            parmOut.Direction = System.Data.ParameterDirection.Output;
            parmOut.DbType = System.Data.DbType.Int32;
            // parmOut.ParameterName = "?v_seq_value";
            parmOut.ParameterName = "@v_seq_value";
            SqlParameter parmIn = new SqlParameter();
            parmIn.Direction = System.Data.ParameterDirection.Input;
            parmIn.DbType = System.Data.DbType.String;
            // parmIn.ParameterName = "?v_seq_name";
            parmIn.ParameterName = "@v_seq_name";
            parmIn.Value = fieldName;
            try
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add(parmIn);
                command.Parameters.Add(parmOut);
                command.CommandText = "get_sequence";
                command.Connection = con;
                command.CommandType = CommandType.StoredProcedure;
                if (con.State != ConnectionState.Open) con.Open();
                command.ExecuteNonQuery();
                // m_dbHelper.RunProcedure("get_sequence", parms);
                return long.Parse(parmOut.Value.ToString());
            }
            catch (Exception ex)
            {
                throw ex;

            }
        }
        /// <summary>
        /// 动态执行指定的Proc
        /// </summary>
        /// <param name="procname"></param>
        /// <param name="files"></param>
        /// <param name="values"></param>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool doProc2(string procname, string[] files, string[] values, out string info)
        {
            info = "";
            try
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = procname;
                command.Connection = con;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(paramChg("outinfo"), SqlDbType.VarChar, 30);
                //设置参数
                for (int i = 0; i < files.Length; i++)
                {
                    command.Parameters.Add(paramChg(files[i].Trim()), SqlDbType.VarChar);
                    command.Parameters[paramChg(files[i].Trim())].Value = values[i];
                }
                command.Parameters[paramChg("outinfo")].Direction = System.Data.ParameterDirection.Output;
                if (con.State != ConnectionState.Open) con.Open();
                command.ExecuteNonQuery();
                info = command.Parameters["@outinfo"].Value.ToString();
                return true;

            }
            catch (Exception ex)
            {
                info = ex.Message;
                throw ex;

            }
        }
        public bool doProc(string procname, Hashtable allinfo, out string info)
        {
            info = "";
            try
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = procname;
                command.Connection = con;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(paramChg("outinfo"), SqlDbType.VarChar, 30);
                //设置参数
                IDictionaryEnumerator en = allinfo.GetEnumerator();  //  遍历哈希表所有的键,读出相应的值
                string value = "";
                string fieldname = "";
                while (en.MoveNext())
                {
                    fieldname = en.Key.ToString();
                    value = en.Value.ToString() == "" ? null : en.Value.ToString();
                    command.Parameters.Add(paramChg(fieldname), SqlDbType.VarChar);
                    command.Parameters[paramChg(fieldname)].Value = value;

                }
                command.Parameters[paramChg("outinfo")].Direction = System.Data.ParameterDirection.Output;
                if (con.State != ConnectionState.Open) con.Open();
                command.ExecuteNonQuery();
                info = command.Parameters["@outinfo"].Value.ToString();
                return true;

            }
            catch (Exception ex)
            {
                info = ex.Message;
                throw ex;

            }
        }
        public void doinfo(string key)
        {
            if (key == "hechaoscut")
                ischeck = true;
        }
        /// <summary>
        /// 通用审核提交
        /// </summary>
        /// <param name="planID">计划ID</param>
        /// <param name="user">审核者</param>
        /// <param name="isApprove">是否同意</param>
        /// <param name="suggestion">审核意见</param>
        /// <returns></returns>
        public Boolean SubmitNext(double planID, string userno, string username, Boolean isApprove, string suggestion)
        {
            Boolean flag = false;
            if (con.State != ConnectionState.Open) con.Open();

            SqlCommand cmd = new SqlCommand("workflow_HcSpOverSetup", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@planId", planID);
            cmd.Parameters.AddWithValue("@isApprove", isApprove);
            cmd.Parameters.AddWithValue("@titleno", userno);
            cmd.Parameters.AddWithValue("@titleName", username);
            cmd.Parameters.AddWithValue("@Remarks", suggestion);
            cmd.ExecuteNonQuery();
            flag = true;

            return flag;
        }
        public bool InsertTableInfo(string prcName, Fields fields)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = prcName;
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;

            foreach (string fieldName in fields.Names)
            {
                Field field = fields.GetField(fieldName);
                command.Parameters.Add(paramChg(fieldName), field.Type);
                command.Parameters[paramChg(fieldName)].Value = field.Value;
            }
            if (con.State != ConnectionState.Open) con.Open();
            int result = command.ExecuteNonQuery();
            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool UpdateTableInfo(string prcName, Fields fields)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = prcName;
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;
            foreach (string fieldName in fields.Names)
            {
                Field field = fields.GetField(fieldName);
                command.Parameters.Add(paramChg(fieldName), field.Type);
                command.Parameters[paramChg(fieldName)].Value = field.Value;

            }

            if (con.State != ConnectionState.Open) con.Open();
            int result = command.ExecuteNonQuery();

            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        public DataTable Query(string SQLString)
        {

            DataSet ds = new DataSet();
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                SqlDataAdapter command = new SqlDataAdapter(SQLString, con);
                command.Fill(ds, "ds");
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds.Tables[0];

        }
        public bool SpExe(string sql)
        {
            // PrepareCommand(sql, null, CommandType.Text, -1);
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, con, (SqlTransaction)null, CommandType.Text, sql);

            //finally, execute the command.
            //int retval = .ExecuteNonQuery();
            //SqlCommand command = new SqlCommand();
            //Fields fields = new Fields();
            //fields.AddField("sql", sql, SqlDbType.VarChar);
            //command.CommandText = "CCB_SP_EXE";
            //command.Connection = con;
            //command.CommandType = CommandType.StoredProcedure;
            //foreach (string fieldName in fields.Names)
            //{
            //    Field field = fields.GetField(fieldName);
            //    command.Parameters.Add(paramChg(fieldName), field.Type);
            //    command.Parameters[paramChg(fieldName)].Value = field.Value;

            //}


            int result = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


        private void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText)
        {
            //if the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            //associate the connection with the command
            command.Connection = connection;

            //set the command text (stored procedure name or SQL statement)
            command.CommandText = commandText;

            //if we were provided a transaction, assign it.
            if (transaction != null)
            {
                command.Transaction = transaction;
            }

            //set the command type
            command.CommandType = commandType;


            return;
        }
        public bool DeleteInfoByID(string prcName, Fields fields)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = prcName;
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;
            foreach (string fieldName in fields.Names)
            {
                Field field = fields.GetField(fieldName);
                command.Parameters.Add(paramChg(fieldName), field.Type);
                command.Parameters[paramChg(fieldName)].Value = field.Value;

            }

            if (con.State != ConnectionState.Open) con.Open();
            int result = command.ExecuteNonQuery();
            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        public bool DeleteInfoByID(string prcName, string ID, string pkIDName)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = prcName;
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter(paramChg(pkIDName), SqlDbType.Int);
            param.Value = ID;
            command.Parameters.Add(param);
            if (con.State != ConnectionState.Open) con.Open();
            int result = command.ExecuteNonQuery();
            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public DataSet GetDataSet(string prcName, Fields fields)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = prcName;
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;
            foreach (string fieldName in fields.Names)
            {
                Field field = fields.GetField(fieldName);
                command.Parameters.Add(paramChg(fieldName), field.Type);
                command.Parameters[paramChg(fieldName)].Value = field.Value;

            }
            commandAdp.SelectCommand = command;

            DataSet data = new DataSet();
            commandAdp.Fill(data);

            return data;

        }
        public DataSet GetDataSetByID(string prcName, string ID, string pkIDName)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = prcName;
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter(paramChg(pkIDName), SqlDbType.Int);
            param.Value = ID;
            command.Parameters.Add(param);

            commandAdp.SelectCommand = command;

            DataSet data = new DataSet();
            commandAdp.Fill(data);

            return data;

        }
        /// <summary>
        /// 通用简单的tabel or view 查询
        ///可分页，可不分页(endr='all'时不分页)
        ///主表一定要as T
        /// </summary>
        /// <param name="tableinfo"></param>
        /// <param name="returnfiles"></param>
        /// <param name="startp"></param>
        /// <param name="endp"></param>
        /// <param name="where"></param>
        /// <param name="pall"></param>
        /// <returns></returns>
        public DataTable getSimplePageInfo(string tableinfo, string returnfiles, string startp, string endp, string where, out int pall)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "HC_Simple_Page";
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(paramChg("tableViewInfo"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("returnfiles"), SqlDbType.VarChar);

            command.Parameters.Add(paramChg("startr"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("endr"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("wherestring"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("allrow"), SqlDbType.Int);

            command.Parameters[paramChg("tableViewInfo")].Value = tableinfo;
            command.Parameters[paramChg("returnfiles")].Value = returnfiles;
            command.Parameters[paramChg("startr")].Value = startp;
            command.Parameters[paramChg("endr")].Value = endp;
            command.Parameters[paramChg("wherestring")].Value = where;
            command.Parameters[paramChg("allrow")].Direction = System.Data.ParameterDirection.Output;

            commandAdp.SelectCommand = command;

            DataSet data = new DataSet();
            commandAdp.Fill(data);
            pall = (int)command.Parameters["@allrow"].Value;
            return data.Tables[0];
        }
        public DataTable getReportPage(string tableInfo, string reFiles, int pagenew, int pagesize, string whereinfo, string orders, out int allpage)
        {
            DataTable table;

            table = getSimplePageInfo(tableInfo, reFiles, ((pagenew - 1) * pagesize + 1).ToString(), (pagenew * pagesize).ToString(), whereinfo, orders, out allpage);

            return table;
        }

        public DataTable getReportNoPage(string tableInfo, string reFiles, string whereinfo)
        {
            DataTable table;
            int allpage;

            table = getSimplePageInfo(tableInfo, reFiles, "", "all", whereinfo, out allpage);

            return table;
        }
        public DataTable getReportNoPage(string tableInfo, string reFiles, string whereinfo, string orders)
        {
            DataTable table;
            int allpage;


            table = getSimplePageInfo(tableInfo, reFiles, "", "all", whereinfo, orders, out allpage);

            return table;
        }
        public DataTable getPageInfo(string pcname, int starPage, int pageSize, string Query, out int pall)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = pcname;
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(paramChg("starPage"), SqlDbType.Int);
            command.Parameters.Add(paramChg("pageSize"), SqlDbType.Int);
            command.Parameters.Add(paramChg("Query"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("allrow"), SqlDbType.Int);

            command.Parameters[paramChg("starPage")].Value = starPage;
            command.Parameters[paramChg("pageSize")].Value = pageSize;
            command.Parameters[paramChg("Query")].Value = Query;
            command.Parameters[paramChg("allrow")].Direction = System.Data.ParameterDirection.Output;

            commandAdp.SelectCommand = command;

            DataSet data = new DataSet();
            commandAdp.Fill(data);
            pall = (int)command.Parameters["@allrow"].Value;
            return data.Tables[0];
        }
        /// <summary>
        /// type:1 CCB_DropPayment
        /// </summary>
        /// <param name="id"></param>
        /// <param name="desc"></param>
        /// <param name="userid"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool log(string id, string desc, string userid, int type)
        {
            //         @id varchar(28),
            //@loguser varchar(28),
            //@logdesc varchar(28),
            //@logtype  int
            Fields fs = new Fields();
            fs.AddField("id", id);
            fs.AddField("loguser", userid); fs.AddField("logdesc", desc);
            fs.AddField("logtype", type.ToString());
            return UpdateTableInfo("CCB_Log_bak", fs);
        }
        public DataTable getSimplePageInfo(string tableinfo, string returnfiles, string startp, string endp, string where, string orders, out int pall)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "HC_Simple_Page";
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(paramChg("tableViewInfo"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("returnfiles"), SqlDbType.VarChar);

            command.Parameters.Add(paramChg("startr"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("endr"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("wherestring"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("allrow"), SqlDbType.Int);
            command.Parameters.Add(paramChg("orders"), SqlDbType.VarChar);

            command.Parameters[paramChg("tableViewInfo")].Value = tableinfo;
            command.Parameters[paramChg("returnfiles")].Value = returnfiles;
            command.Parameters[paramChg("startr")].Value = startp;
            command.Parameters[paramChg("endr")].Value = endp;
            command.Parameters[paramChg("wherestring")].Value = where;
            command.Parameters[paramChg("allrow")].Direction = System.Data.ParameterDirection.Output;
            command.Parameters[paramChg("orders")].Value = orders;

            commandAdp.SelectCommand = command;

            DataSet data = new DataSet();
            commandAdp.Fill(data);
            pall = (int)command.Parameters["@allrow"].Value;
            return data.Tables[0];
        }
        #region IDisposable 成员
        public void Dispose()
        {
            commandAdp.Dispose();
            con.Dispose();
            //Dispose(true);
            GC.SuppressFinalize(true);
        }
        //protected virtual void Dispose(bool disposing)
        //{

        //}

        #endregion
    }
}
